package com.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.core.Helper;
import com.core.SeatManager.SeatCode;
import com.objects.Employee;
import com.objects.Seat;

public class SeatDAOImpl implements SeatDAO{
	private Connection conn = null;
	@Override
	public Seat searchSeat(String seatCode) throws SQLException {
		DAOConnector daoConn = new DAOConnector();
		CallableStatement proc = null;
		Seat seat = new Seat();
		
		   try
		   {
			  conn  = daoConn.getConnection();
		      proc = conn.prepareCall("{ call GetSeatDetails(?) }");
		      proc.setInt(1, Integer.parseInt(seatCode));
		      
		      ResultSet rs = proc.executeQuery();
		      
		        while (rs.next()){
		            seat.setSeatID(rs.getString("seat_Id"));
		            seat.setEmployeeID(rs.getString("employee_Id"));
		            seat.setSeatCode(rs.getString("seat_Code"));
		            seat.setSeatType(rs.getString("seat_Type"));
		            seat.setQuadrant(rs.getString("quadrant"));
		            seat.setPhoneLocalNumber(rs.getString("local_no"));
		           }
		        rs.close();
		   }
		   finally
		   {
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      conn.close();
		   }
		return seat;
	}

	@Override
	public String updateEmployeeSeat(Employee employee, String newSeatCode) throws SQLException {
		DAOConnector daoConn = new DAOConnector();
		CallableStatement proc = null;
		String str = "";
		//boolean status = false;
		
		   try
		   {
			  conn  = daoConn.getConnection();
		      proc = conn.prepareCall("{ call UpdataEmployeeSeat(?,?,?,?,?,?,?,?) }");
		      proc.setInt(1, Integer.parseInt(employee.getEmployeeID()));
		      proc.setString(2, employee.getSeatCode());
		      proc.setString(3, newSeatCode);
		      proc.setString(4, Helper.parseSeatCode(employee.getSeatCode(), SeatCode.QUADRANT)); //Quadrant
		      //proc.setString(5, Helper.parseSeatCode(employee.getSeatCode(), SeatCode.LOCATIONCODE)); //locationCode	
		      proc.setInt(5, Integer.parseInt(Helper.parseSeatCode(employee.getSeatCode(), SeatCode.FLOOR))); //locatioion id 3F
		      proc.setInt(6, 1);
		      proc.setInt(7, Integer.parseInt(Helper.parseSeatCode(employee.getSeatCode(), SeatCode.COLUMN))); //Column
		      proc.setInt(8, Integer.parseInt(Helper.parseSeatCode(employee.getSeatCode(), SeatCode.ROW))); //Row
		    		      
		      int stat  = proc.executeUpdate();
		      str = String.valueOf(stat);
		     /* while (rs.next()){
		    	  str = String.valueOf(rs.getObject(1));
		        }*/
		   }
		   finally
		   {
			   try{
			   if (proc != null) {	               
	                	proc.close();
	               
	            }
	            if (conn != null) {
	                  conn.close();
	            }
			   } catch (SQLException e) {
                   System.err.println("SQLException: " + e.getMessage());
                   conn.close();
			   }
		     
		   }
		return str;
		
	}

	@Override
	public boolean deleteEmployeeSeat(Employee employee) throws SQLException{
		DAOConnector daoConn = new DAOConnector();
		CallableStatement proc = null;

		boolean status = false;
		
		   try
		   {
			  conn  = daoConn.getConnection();
		      proc = conn.prepareCall("{ call DeleteEmployeeSeat(?,?) }");
		      proc.setInt(1, Integer.parseInt(employee.getEmployeeID()));
		      proc.setString(2, employee.getSeatCode());
	      
		      status = proc.execute();
		   }
		   finally
		   {
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      conn.close();
		   }
		return status;
	}
	
	@Override
	public List<Seat> searchSeatMap(String quadrant) throws SQLException {
		DAOConnector daoConn = new DAOConnector();
		CallableStatement proc = null;
		Seat seat = new Seat();
		List<Seat> seats = new ArrayList<Seat>();
		
		   try
		   {
			  conn  = daoConn.getConnection();
		      proc = conn.prepareCall("{ call GetSeatMapDetails(?) }");
		      proc.setInt(1, Integer.parseInt(quadrant));
		      
		      ResultSet rs = proc.executeQuery();
		      
		        while (rs.next())
		        {
		            seat.setSeatID(rs.getString("seat_Id"));
		            seat.setEmployeeID(rs.getString("employee_Id"));
		            seat.setSeatCode(rs.getString("seat_Code"));
		            seat.setSeatType(rs.getString("seat_Type"));
		            seat.setQuadrant(rs.getString("quadrant"));
		            seat.setPhoneLocalNumber(rs.getString("local_no"));
		            seats.add(seat);
		           }
		        rs.close();
		   }
		   finally
		   {
		      try
		      {
		         proc.close();
		      }
		      catch (SQLException e) {}
		      conn.close();
		   }
		return seats;
	}

}
